package jp.ac.u_tokyo.dch.dao.user;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.xwork.StringUtils;


import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.User;
import jp.ac.u_tokyo.dch.model.db.UserGroup;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;

/**
 * @version 1.1
**/
public class UserGroupInfoDao {


	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 *
	 * @param DBコネクションオブジェクト
	 */
	public UserGroupInfoDao(DBConnection dbconn) {
		this.dbconn = dbconn;
	}

	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<User> selectByGroupId(String id) throws DBException {

		List<User> userList = new ArrayList<User>();

		StringBuilder sqlQuery = new StringBuilder();

		sqlQuery.append("select * from user_group_info INNER JOIN users on");
		sqlQuery.append(" user_group_info.userid = users.id ");
		sqlQuery.append(" where user_group_info.groupid = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				User user = new User();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setFrontLevel(rs.getString("frontlevel"));
				user.setManageLevel(rs.getString("managelevel"));
				user.setCreateUser(rs.getString("createuser"));
				user.setCreateDate(rs.getString("createdate"));
				user.setUpdateUser(rs.getString("updateuser"));
				user.setUpdateDate(rs.getString("updatedate"));

				userList.add(user);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return userList;
	}

	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<String> selectGroupIdByUserId(String id) throws DBException {

		List<String> groupList = new ArrayList<String>();

		StringBuilder sqlQuery = new StringBuilder();

		sqlQuery.append("select * from user_group_info INNER JOIN user_group on");
		sqlQuery.append(" user_group_info.groupid = user_group.id ");
		sqlQuery.append(" where user_group_info.userid = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				groupList.add(rs.getString("groupid"));
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return groupList;
	}

	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<String> selectGroupNameByUserId(String id) throws DBException {

		List<String> groupNameList = new ArrayList<String>();

		StringBuilder sqlQuery = new StringBuilder();

		sqlQuery.append("select * from user_group_info INNER JOIN user_group on");
		sqlQuery.append(" user_group_info.groupid = user_group.id ");
		sqlQuery.append(" where user_group_info.userid = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				groupNameList.add(rs.getString("name"));
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return groupNameList;
	}

	/**
	 * ユーザグループリストを取得する
	 * @return ユーザグループリスト
	 * @throws DBException 例外
	 */
	public List<UserGroup> selectByUserId(String id) throws DBException {

		List<UserGroup> groupList = new ArrayList<UserGroup>();

		StringBuilder sqlQuery = new StringBuilder();

		sqlQuery.append("select * from user_group_info INNER JOIN user_group on");
		sqlQuery.append(" user_group_info.groupid = user_group.id ");
		sqlQuery.append(" where user_group_info.userid = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try {
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				UserGroup userGroup = new UserGroup();
				userGroup.setId(rs.getString("id"));
				userGroup.setName(rs.getString("name"));
				userGroup.setCreateUser(rs.getString("createuser"));
				userGroup.setCreateDate(rs.getString("createdate"));
				userGroup.setUpdateUser(rs.getString("updateuser"));
				userGroup.setUpdateDate(rs.getString("updatedate"));

				groupList.add(userGroup);
			}

		} catch (DBException dbe) {
			throw dbe;
		} finally {
			CommonDBConnection.closeResource(ps, rs);
		}
		return groupList;
	}

	public int deleteByGroupId(String groupid) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "DELETE FROM user_group_info WHERE groupid = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++,groupid);
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}

		return reslut;
	}

	public int deleteByUserId(String userId) throws DBException {

		int reslut = 0;
		CustomPreparedStatement ps = null;

		try{
			String query = "DELETE FROM user_group_info WHERE userid = ?";
			ps = dbconn.prepareStatement(query);

			int index = 1;
			ps.setString(index++,userId);
			reslut = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
		return reslut;
	}

	/**
	 * 挿入する
	 * @param userGroup ユーザグループ
	 * @throws DBException 例外
	 */
	public int[] insert(String userId, List<String> groupList) throws DBException {

		if (groupList == null) {

			return null;
		}

		int[] reslut = null;
		CustomPreparedStatement ps = null;

		try{
			String query = "INSERT INTO user_group_info (userId,groupid)  VALUES (?,?)";
			ps = dbconn.prepareStatement(query);

			for (String userGroupId : groupList) {

				if (StringUtils.isNotEmpty(userGroupId)) {
					int index = 1;
					ps.setString(index++, userId);
					ps.setString(index++, userGroupId);

					ps.addBatch();
				}
			}
			reslut = ps.executeBatch();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
		return reslut;
	}
}
